package kr.hkit.jenny.coffee.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.jenny.coffee.dto.SalesRecord;
import kr.hkit.myframework.db.DbUtil;

public class CoffeeDao {
	private Connection connection;
	private PreparedStatement pstmt;

	public CoffeeDao(Connection connection) {
		this.connection = connection;
	}
	
	public void insertItem(SalesRecord item) throws SQLException {
		String sql = "insert into sales values(?, ?, ?, ?)";// 이 형태의 sql쿼리
		pstmt = connection.prepareStatement(sql);// ?를 쉽게 쓰기 위한 방법
		pstmt.setString(1, item.getPdCode());
		pstmt.setInt(2, item.getPdPrice());
		pstmt.setInt(3, item.getAmount());
		pstmt.setDouble(4, item.getpMargin());
		System.out.println(pstmt);
		pstmt.executeUpdate();// 성공하면 1/ 실제 db에 적용하기
		DbUtil.close(pstmt);
	}
	
	public ArrayList<String> getAllCodes() throws SQLException{
		ArrayList<String> list = new ArrayList<>();
		String sql = "select pdCode from product";
		pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();// 결과값 포인터
		while (rs.next()) {// 다음 레코드가 있니?
			list.add(rs.getString("pdCode"));
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}
	
	public String getName(String code) throws SQLException{
		String name = null;
		String sql = "select pdName from product where pdCode = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, code);
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			name = rs.getString(1);
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return name;
	}

	public ArrayList<SalesRecord> listItems() throws SQLException {
		ArrayList<SalesRecord> list = new ArrayList<>();
		String sql = "select p.pdCode, p.pdName, s.pdPrice, s.amount, s.pMargin from product p, sales s where p.pdCode = s.pdCode";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			SalesRecord s = new SalesRecord();
			s.setPdCode(rs.getString(1));
			s.setPdName(rs.getString(2));
			s.setPdPrice(rs.getInt(3));
			s.setAmount(rs.getInt(4));
			s.setpMargin(rs.getDouble(5));
			s.setSellingPrice();
			s.setTax();
			s.setSupply();
			s.setMargin();
			list.add(s);
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}
	
	
}
